package com.vteba.utils.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.vteba.utils.json.Bean;

/**
 * 导出Excel文件。兼容Excel 2003和2007。
 * 
 * @author yinlei
 * @see
 * @since 2012年5月20日 下午4:39:09
 */
public class ExportExcel {
	private String dataFormat	= "yyyy/MM/dd HH:mm:ss";// 日期格式
	private String[] titles;// 标题
	private int[] columnWidths;// 每一列的宽度（字符数）
	private String sheetName = "sheet1";
	private boolean xlsx2007;
	private List<Object[]> dataList;
	private DataFormat format;

	public ExportExcel() {
	}
	
	public ExportExcel(String[] titles) {
		super();
		this.titles = titles;
	}

	public ExportExcel(String[] titles, int[] columnWidths) {
		super();
		this.titles = titles;
		this.columnWidths = columnWidths;
	}

	public ExportExcel(String[] titles, int[] columnWidths, boolean xlsx2007) {
		super();
		this.titles = titles;
		this.columnWidths = columnWidths;
		this.xlsx2007 = xlsx2007;
	}

	public ExportExcel(String[] titles, boolean xlsx2007, List<Object[]> dataList) {
		super();
		this.titles = titles;
		this.xlsx2007 = xlsx2007;
		this.dataList = dataList;
	}

	public ExportExcel(String[] titles, int[] columnWidths, boolean xlsx2007,
			List<Object[]> dataList) {
		super();
		this.titles = titles;
		this.columnWidths = columnWidths;
		this.xlsx2007 = xlsx2007;
		this.dataList = dataList;
	}

	/**
	 * 设置导出的Excel标题
	 * @param titles the title
	 */
	public void setTitles(String[] titles) {
		this.titles = titles;
	}

	/**
	 * 设置导出的Excel数据
	 * @param dataList the dataList
	 */
	public void setDataList(List<Object[]> dataList) {
		this.dataList = dataList;
	}

	/**
	 * 设置每一列的宽度（字符数），如果不设置，默认10个字符的宽度
	 * @param columnWidths the columnWidths
	 */
	public void setColumnWidths(int[] columnWidths) {
		this.columnWidths = columnWidths;
	}

	/**
	 * sheet工作表名字
	 * @param sheetName the sheetName
	 */
	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	/**
	 * 是否是Excel 2007
	 * @return the xlsx2007
	 */
	public boolean isXlsx2007() {
		return xlsx2007;
	}

	/**
	 * 设置是否是Excel 2007
	 * @param xlsx2007 the xlsx2007
	 */
	public void setXlsx2007(boolean xlsx2007) {
		this.xlsx2007 = xlsx2007;
	}

	/**
	 * 根据数据，导出Excel
	 * @return Excel文件
	 */
	public Workbook toExcel() {
		Workbook wb = createWorkbook();
		Sheet sheet = wb.createSheet(this.sheetName);
		createExcelHeader(sheet);
		createExcel(wb, sheet, this.dataList);
		return wb;
	}
	
	/**
	 * 根据数据，导出Excel
	 * @param dataList 要导出的数据
	 * @param sheetname 工作表名字
	 * @return Excel文件
	 */
	public Workbook toExcel(List<Object[]> dataList) {
		Workbook wb = createWorkbook();
		Sheet sheet = wb.createSheet(this.sheetName);
		createExcelHeader(sheet);
		createExcel(wb, sheet, dataList);
		return wb;
	}
	
	/**
	 * 根据数据，导出Excel
	 * @param dataList 要导出的数据
	 * @param sheetname 工作表名字
	 * @param titles 列标题
	 * @return Excel文件
	 */
	public Workbook toExcel(List<Object[]> dataList, String sheetname, String[] titles) {
		Workbook wb = createWorkbook();
		Sheet sheet = wb.createSheet(sheetname);
		createExcelHeader(sheet, titles);
		createExcel(wb, sheet, dataList);
		return wb;
	}
	
	/**
	 * 根据是否是Excel 2007，创建Excel文件
	 * @return Excel工作表
	 */
	private Workbook createWorkbook() {
		Workbook wb = null;
		if (isXlsx2007()) {
			wb = new XSSFWorkbook();
		} else {
			wb = new HSSFWorkbook();
		}
		// 初始化自定义日期格式
		this.format = wb.createDataFormat();
		return wb;
	}
	
	/**
	 * 创建表头（标题），同时设置excel每一列的宽度
	 * @param sheet 工作表sheet
	 */
	private void createExcelHeader(Sheet sheet) {
		for (int i = 0; i < titles.length; i++) {
			if (columnWidths != null) {
				sheet.setColumnWidth(i, columnWidths[i] * 256);
			} else {
				sheet.setColumnWidth(i, 15 * 256);
			}
			setStringValue(sheet, 0, i, titles[i]);
		}
	}
	
	/**
	 * 创建表头（标题），同时设置excel每一列的宽度
	 * @param sheet 工作表sheet
	 * @param titles 标题
	 */
	private void createExcelHeader(Sheet sheet, String[] titles) {
		for (int i = 0; i < titles.length; i++) {
			if (columnWidths != null) {
				sheet.setColumnWidth(i, columnWidths[i] * 256);
			} else {
				sheet.setColumnWidth(i, 10 * 256);
			}
			setStringValue(sheet, 0, i, titles[i]);
		}
	}
	
	/**
	 * 根据数据产生Excel
	 * @param wb Excel
	 * @param sheet 工作表
	 * @param dataList 要导出的数据
	 */
	private void createExcel(Workbook wb, Sheet sheet, List<Object[]> dataList) {
		for (int i = 1; i <= dataList.size(); i++) {
			Object[] object = dataList.get(i - 1);
			for (int j = 0; j < object.length; j++) {
				this.setCellValue(wb, sheet, i, j, object[j]);
			}
		}
	}
	
	/**
	 * 设置cell值
	 * @param wb excel
	 * @param sheet 工作表
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @param value 单元格值
	 */
	private void setCellValue(Workbook wb, Sheet sheet, int rowNum, int colNum, Object value) {
		if (value != null) {
			if (value instanceof Number) {
				setDoubleValue(sheet, rowNum, colNum, Double.valueOf(value.toString()));
			} else if (value instanceof String) {
				setStringValue(sheet, rowNum, colNum, value.toString());
			} else if (value instanceof Date) {
				CellStyle cellStyle = wb.createCellStyle();
				setDateValue(sheet, cellStyle, rowNum, colNum, (Date) value);
			} else if (value instanceof Boolean) {
				setBooleanValue(sheet, rowNum, colNum, (Boolean)value);
			}
		}
	}
	
	/**
	 * 设置double型值，包含所有的数字类型。Excel内建所有的数字都是double存储，包括日期。
	 * @param sheet 工作表
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @param value Double值
	 */
	private void setDoubleValue(Sheet sheet, int rowNum, int colNum, Double value) {
		Cell cell = this.getOrCreateCell(sheet, rowNum, colNum);
		cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}
	
	/**
	 * 设置日期型值
	 * @param sheet sheet
	 * @param cellStyle 单元格样式
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @param value 日期值
	 */
	private void setDateValue(Sheet sheet, CellStyle cellStyle, int rowNum, int colNum, Date value) {
		// 指定日期显示格式
		cellStyle.setDataFormat(format.getFormat(dataFormat));
		Cell cell = this.getOrCreateCell(sheet, rowNum, colNum);
		// 设定单元格日期显示格式
		cell.setCellStyle(cellStyle);
		cell.setCellValue(value);
	}
	
	/**
	 * 设置string型值
	 * @param sheet sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @param value String值
	 */
	private void setStringValue(Sheet sheet, int rowNum, int colNum, String value) {
		Cell cell = this.getOrCreateCell(sheet, rowNum, colNum);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		RichTextString str = null;
		if (isXlsx2007()) {
			str = new XSSFRichTextString(value);
		} else {
			str = new HSSFRichTextString(value);
		}
		cell.setCellValue(str);
	}

	/**
	 * 设置boolean类型值
	 * @param sheet sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 * @param value boolean值
	 */
	private void setBooleanValue(Sheet sheet, int rowNum, int colNum, boolean value) {
		Cell cell = this.getOrCreateCell(sheet, rowNum, colNum);
		cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
		cell.setCellValue(value);
	}
	
	/**
	 * 获得指定行列的cell，如果没有，将创建一个。
	 * @param sheet sheet
	 * @param rowNum 行数
	 * @param colNum 列数
	 */
	private Cell getOrCreateCell(Sheet sheet, int rowNum, int colNum) {
		Row row = sheet.getRow(rowNum);
		if (null == row) {
			row = sheet.createRow(rowNum);
		}
		Cell cell = row.getCell(colNum);
		if (null == cell) {
			cell = row.createCell(colNum);
		}
		return cell;
	}

	public void setDataFormat(String dataFormat) {
		this.dataFormat = dataFormat;
	}
	
	public static void main(String[] args) {
		File file = new File("D:\\export2.xlsx");
		String[] columns = { "userName", "date", "dates", "index", "integerColumn", "test", "boolType", "longType" };
		try {
			FileInputStream fileInputStream = new FileInputStream(file);
			
			ImportExcel<Bean> excel = new ImportExcel<>();
			excel.setExcelStream(fileInputStream);
			excel.setBeanType(Bean.class);
			excel.setColumns(columns);
			excel.setXlsx2007(true);
			
			List<Bean> beanList = excel.toList();
			
			List<Object[]> exportList = new ArrayList<Object[]>();
			for (Bean bean : beanList) {
				Object[] object = new Object[8];
				object[0] = bean.getUserName();
				object[1] = bean.getDate();
				object[2] = bean.getDates();
				object[3] = bean.getIndex();
				object[4] = bean.getIntegerColumn();
				object[5] = bean.isTest();
				object[6] = bean.getBoolType();
				object[7] = bean.getLongType();
				exportList.add(object);
			}
			ExportExcel exportExcel = new ExportExcel(columns, true, exportList);
			int[] columnWidths = {10, 25, 10, 10, 10, 10, 10, 10};
			exportExcel.setColumnWidths(columnWidths);
			Workbook workbook = exportExcel.toExcel();
			File file2 = new File("d://export4.xlsx");
			FileOutputStream fos = new FileOutputStream(file2);
			workbook.write(fos);
			fos.flush();
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}
